{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\mpl_finance.py:16: DeprecationWarning: \n",
      "\n",
      "  =================================================================\n",
      "\n",
      "   WARNING: `mpl_finance` is deprecated:\n",
      "\n",
      "    Please use `mplfinance` instead (no hyphen, no underscore).\n",
      "\n",
      "    To install: `pip install --upgrade mplfinance` \n",
      "\n",
      "   For more information, see: https://pypi.org/project/mplfinance/\n",
      "\n",
      "  =================================================================\n",
      "\n",
      "  __warnings.warn('\\n\\n  ================================================================='+\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import datetime\n",
    "from influxdb import InfluxDBClient\n",
    "import talib as  ta\n",
    "import seaborn as sns\n",
    "import numpy as np\n",
    "import warnings\n",
    "from matplotlib import pyplot as plt\n",
    "warnings.filterwarnings('ignore')\n",
    "import sys\n",
    "sys.path.append(\"..\")\n",
    "\n",
    "from common import candle\n",
    "from common import dbhelper\n",
    "\n",
    "client = InfluxDBClient('192.168.3.108',8086)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "def get_atr(df, N=14):\n",
    "    try:\n",
    "        df['tr'] = 0\n",
    "        # df['atr'] = 0\n",
    "        for i in range(len(df)):\n",
    "            tr =  df.high[i] - df.low[i]\n",
    "            h_tr = df.preclose[i] -  df.high[i] \n",
    "            l_tr = df.preclose[i] -  df.low[i]\n",
    "            df.tr[i] = max(tr, max(h_tr, l_tr))\n",
    "\n",
    "        df['atr'] = ta.SMA(df['tr'], N)\n",
    "        \n",
    "        return df\n",
    "    except Exception as e:\n",
    "        print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "current_time = datetime.datetime.now()\n",
    "current_time\n",
    "dbname = \"KlineIndex\"\n",
    "\n",
    "sqlmin = \"select * from rp_month.KlineMin1 where symbol='HSI' and  time > '{}-{}-{} 09:00:00'\".format(\n",
    "        current_time.year,\n",
    "        str(current_time.month).rjust(2, '0'),\n",
    "        str(current_time.day).rjust(2, '0'))\n",
    "\n",
    "sqlday = \"select * from KlineDay where symbol='HSI' and  time > '2009-12-10 00:00:00'\".format(\n",
    "        current_time.year)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "rsday = client.query(sqlday, database=dbname)\n",
    "df_day = pd.DataFrame(rsday.get_points())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_atr = get_atr(df_day)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>time</th>\n",
       "      <th>close</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>open</th>\n",
       "      <th>preclose</th>\n",
       "      <th>symbol</th>\n",
       "      <th>value</th>\n",
       "      <th>vol</th>\n",
       "      <th>tr</th>\n",
       "      <th>atr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2919</th>\n",
       "      <td>2021-10-26T00:00:00Z</td>\n",
       "      <td>26038.27</td>\n",
       "      <td>26234.94</td>\n",
       "      <td>25905.08</td>\n",
       "      <td>26234.94</td>\n",
       "      <td>26132.03</td>\n",
       "      <td>HSI</td>\n",
       "      <td>1.139893e+11</td>\n",
       "      <td>9900247363</td>\n",
       "      <td>329</td>\n",
       "      <td>339.214286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2920</th>\n",
       "      <td>2021-10-27T00:00:00Z</td>\n",
       "      <td>25628.74</td>\n",
       "      <td>25795.17</td>\n",
       "      <td>25555.17</td>\n",
       "      <td>25795.17</td>\n",
       "      <td>26038.27</td>\n",
       "      <td>HSI</td>\n",
       "      <td>1.206878e+11</td>\n",
       "      <td>10786854190</td>\n",
       "      <td>483</td>\n",
       "      <td>351.142857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2921</th>\n",
       "      <td>2021-10-28T00:00:00Z</td>\n",
       "      <td>25555.73</td>\n",
       "      <td>25736.38</td>\n",
       "      <td>25473.16</td>\n",
       "      <td>25648.42</td>\n",
       "      <td>25628.74</td>\n",
       "      <td>HSI</td>\n",
       "      <td>1.293107e+11</td>\n",
       "      <td>12083500051</td>\n",
       "      <td>263</td>\n",
       "      <td>322.285714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2922</th>\n",
       "      <td>2021-10-29T00:00:00Z</td>\n",
       "      <td>25448.68</td>\n",
       "      <td>25499.42</td>\n",
       "      <td>25446.89</td>\n",
       "      <td>25466.86</td>\n",
       "      <td>25555.73</td>\n",
       "      <td>HSI</td>\n",
       "      <td>5.405534e+09</td>\n",
       "      <td>344117657</td>\n",
       "      <td>108</td>\n",
       "      <td>294.428571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2923</th>\n",
       "      <td>2021-11-08T00:00:00Z</td>\n",
       "      <td>24763.77</td>\n",
       "      <td>24837.77</td>\n",
       "      <td>24633.57</td>\n",
       "      <td>24743.54</td>\n",
       "      <td>24870.51</td>\n",
       "      <td>HSI</td>\n",
       "      <td>1.219399e+11</td>\n",
       "      <td>9963274699</td>\n",
       "      <td>236</td>\n",
       "      <td>299.785714</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      time     close      high       low      open  preclose  \\\n",
       "2919  2021-10-26T00:00:00Z  26038.27  26234.94  25905.08  26234.94  26132.03   \n",
       "2920  2021-10-27T00:00:00Z  25628.74  25795.17  25555.17  25795.17  26038.27   \n",
       "2921  2021-10-28T00:00:00Z  25555.73  25736.38  25473.16  25648.42  25628.74   \n",
       "2922  2021-10-29T00:00:00Z  25448.68  25499.42  25446.89  25466.86  25555.73   \n",
       "2923  2021-11-08T00:00:00Z  24763.77  24837.77  24633.57  24743.54  24870.51   \n",
       "\n",
       "     symbol         value          vol   tr         atr  \n",
       "2919    HSI  1.139893e+11   9900247363  329  339.214286  \n",
       "2920    HSI  1.206878e+11  10786854190  483  351.142857  \n",
       "2921    HSI  1.293107e+11  12083500051  263  322.285714  \n",
       "2922    HSI  5.405534e+09    344117657  108  294.428571  \n",
       "2923    HSI  1.219399e+11   9963274699  236  299.785714  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_atr.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
